const { T_exam_target_to_question } = require('../models/t_exam_target_to_question')
const { T_exam_target } = require('../models/t_exam_target')
const XLSX = require('xlsx')
const { sequelize } = require('../utill/getSequelize')
const Workbook = require('xlsx-populate/lib/Workbook')
const XlsxPopulate = require('xlsx-populate');
const Sheet = require('xlsx-populate/lib/Sheet')
const { lock } = require('../router/T_question')
// const { INITIALLY_DEFERRED } = require('sequelize/types/deferrable')



const columnEnum = {
    1: 'A',
    2: 'B',
    3: 'C',
    4: 'D',
    5: 'E',
    6: 'F',
    7: 'G',
    8: 'H',
    9: 'I',
    10: 'J',
    11: 'K',
    12: 'L',
    13: 'M',
    14: 'N',
    15: 'O',
    16: 'P',
    17: 'Q',
    18: 'R',
    19: 'S',
    20: 'T',
    21: 'U',
    22: 'V',
    23: 'W',
    24: 'X',
    25: 'Y',
    26: 'Z',
    27: 'AA',
    28: 'AB',
    29: 'AC',
    30: 'AD',
    31: 'AE',
    32: 'AF',
    33: 'AG',
    34: 'AH',
    35: 'AI',
    36: 'AJ',
    37: 'AK',
    38: 'AL',
    39: 'AM',
    40: 'AN',
    41: 'AO',
    42: 'AP',
    43: 'AQ',
    44: 'AR',
    45: 'AS',
    46: 'AT',
    47: 'AU',
    48: 'AV',
    49: 'AW',
    50: 'AX',
    51: 'AY',
    52: 'AZ',
    53: 'BA',
    54: 'BB',
    55: 'BC',
    56: 'BD',
    57: 'BE',
    58: 'BF',
    59: 'BG',
    60: 'BH',
    61: 'BI',
    62: 'BJ',
    63: 'BK',
    64: 'BL',
    65: 'BM',
    66: 'BN',
    67: 'BO',
    68: 'BP',
    69: 'BQ',
    70: 'BR',
    71: 'BS',
    72: 'BT',
    73: 'BU',
    74: 'BV',
    75: 'BW',
    76: 'BX',
    77: 'BY',
    78: 'BZ',
    79: 'CA',
    80: 'CB',
    81: 'CC',
    82: 'CD',
    83: 'CE',
    84: 'CF',
    85: 'CG',
    86: 'CH',
    87: 'CI',
    88: 'CJ',
    89: 'CK',
    90: 'CL',
    91: 'CM',
    92: 'CN',
    93: 'CO',
    94: 'CP',
    95: 'CQ',
    96: 'CR',
    97: 'CS',
    98: 'CT',
    99: 'CU',
    100: 'CV'
};

function getKeyByValue(object, value) {
    return Object.keys(object).find(key => object[key] === value);
}

exports.outputAchieveTargetXlsx = async (req, res) => {
    const paperType = req.query.paperType
    const ACSId = req.query.ACSId
    const paperInfo = await T_exam_target_to_question.findAll({
        where: {
            paperType: paperType,
            t_user_and_a_c_s_id: ACSId
        },
        attributes: [
            'questionType',
            'questionNum',
            [sequelize.col('target.target_name'), 'targetName']
        ],
        include: [
            {
                model: T_exam_target,
                as: 'target',
                attributes: []
            }
        ],
        // group: ['questionType', 'target.target_name'] // 将 'targetDesc' 修改为 'target.target_desc'
    });

    const targetNum = await T_exam_target.findAll({
        where: {
            t_user_and_a_c_s_id: ACSId
        },
    })
    // console.log(targetNum, (await targetNum).length);

    const someInfo = {}
    const numInfo = {}
    paperInfo.forEach(obj => {
        if (!someInfo[obj.questionType]) {
            someInfo[obj.questionType] = [];
        }
        if (!numInfo[obj.questionType]) {
            numInfo[obj.questionType] = [];
        }
        someInfo[obj.questionType].push("(" + obj.dataValues.questionNum + "题)" + obj.dataValues.targetName)
        numInfo[obj.questionType].push(obj.dataValues.questionNum)
    })
    console.log("mes", someInfo);
    console.log("数目", numInfo);




    const infoKey = Object.keys(someInfo)
    // const initArray = ['姓名', '学号'].concat(infoKey)
    const initArray = infoKey
    console.log(initArray);

    // 初始化列标题数组s
    const workbook = XlsxPopulate.fromBlankAsync()
        .then(workbook => {
            const sheet = workbook.sheet(0)
            let add = 0
            sheet.cell("A3").value("姓名")
            sheet.cell("B3").value("学号")
            let initNum = 'C'
            const columnWidth = 20;
            for (let k = 1; k <= targetNum.length; k++) {
                sheet.cell(initNum + "3").value("课堂表现" + k)
                sheet.cell(initNum + "3").style({
                    fontColor: "#0d0d0d", // 字体颜色为黑色
                    fontSize: 15, // 字体大小为15
                    bold: true, // 加粗
                    italic: true, // 倾斜
                    underline: true, // 下划线
                    border: {
                        bottom: { style: "thin", color: "000000" }, // 底部边框
                        top: { style: "thin", color: "000000" }, // 顶部边框
                        left: { style: "thin", color: "000000" }, // 左侧边框
                        right: { style: "thin", color: "000000" } // 右侧边框
                    },
                    horizontalAlignment: "center", // 水平居中
                    verticalAlignment: "middle" // 垂直居中
                })
                sheet.column(initNum).width(columnWidth);
                initNum = columnEnum[parseInt(getKeyByValue(columnEnum, initNum)) + parseInt(1)]
                console.log("k=", k, "initNum = ", initNum);
            }
            for (let k = 1; k <= targetNum.length; k++) {
                sheet.cell(initNum + "3").value("课堂作业" + k)

                sheet.cell(initNum + "3").style({
                    fontColor: "#0d0d0d", // 字体颜色为黑色
                    fontSize: 15, // 字体大小为15
                    bold: true, // 加粗
                    italic: true, // 倾斜
                    underline: true, // 下划线
                    border: {
                        bottom: { style: "thin", color: "000000" }, // 底部边框
                        top: { style: "thin", color: "000000" }, // 顶部边框
                        left: { style: "thin", color: "000000" }, // 左侧边框
                        right: { style: "thin", color: "000000" } // 右侧边框
                    },
                    horizontalAlignment: "center", // 水平居中
                    verticalAlignment: "middle" // 垂直居中
                })


                sheet.column(initNum).width(columnWidth);

                initNum = columnEnum[parseInt(getKeyByValue(columnEnum, initNum)) + parseInt(1)]
            }
            const afterinit = initNum
            // sheet.cell("C3").value("平时表现")
            // sheet.cell("D3").value("作业成绩")


            for (let i = 0; i < initArray.length; i++) {
                const temp = numInfo[initArray[i]].reduce((acc, cur) => acc + cur, 0);
                const temp2 = getKeyByValue(columnEnum, initNum);
                const cell_range = initNum + "1" + ":" + columnEnum[parseInt(temp) + parseInt(temp2) - 1] + "1";
                sheet.cell(initNum + "1").value(initArray[i]);

                // 将内容居中
                sheet.range(cell_range).merged(true);
                const cell = sheet.cell(initNum + "1");
                cell.style({
                    fontColor: "#0d0d0d", // 字体颜色为黑色
                    fontSize: 15, // 字体大小为14
                    bold: true, // 加粗
                    italic: true, // 倾斜
                    underline: true, // 下划线
                    horizontalAlignment: "center", // 水平居中
                    verticalAlignment: "middle", // 垂直居中
                    // numberFormat: "0.00", // 数字格式
                    // protection: true // 启用保护
                });

                // 设置边框
                sheet.range(cell_range).style({
                    border: {
                        bottom: { style: "thin", color: "000000" }, // 底部边框
                        top: { style: "thin", color: "000000" }, // 顶部边框
                        left: { style: "thin", color: "000000" }, // 左侧边框
                        right: { style: "thin", color: "000000" } // 右侧边框
                    }
                });

                initNum = columnEnum[parseInt(temp) + parseInt(temp2)];
                add += initArray.length;
            }

            initNum = afterinit
            let init = afterinit
            let t = 1
            // const allValues = Object.values(someInfo).flat();
            for (let j = 0; j < initArray.length; j++) {

                for (let h = 0; h < numInfo[initArray[j]].length; h++) {
                    const temp = numInfo[initArray[j]][h];
                    const temp2 = getKeyByValue(columnEnum, initNum)
                    const cell_range = initNum + "2" + ":" + columnEnum[parseInt(temp) + parseInt(temp2) - 1] + "2"

                    sheet.cell(initNum + "2").value(someInfo[initArray[j]][h])


                    console.log(someInfo[initArray[j]][h]);
                    sheet.range(cell_range).merged(true)
                    const cell = sheet.cell(initNum + "2")
                    // 设置边框
                    cell.style({
                        fontColor: "#0d0d0d", // 字体颜色为黑色
                        fontSize: 15, // 字体大小为14
                        bold: true, // 加粗
                        italic: true, // 倾斜
                        underline: true, // 下划线
                        border: {
                            bottom: { style: "thin", color: "000000" }, // 底部边框
                            top: { style: "thin", color: "000000" }, // 顶部边框
                            left: { style: "thin", color: "000000" }, // 左侧边框
                            right: { style: "thin", color: "000000" } // 右侧边框
                        },
                        horizontalAlignment: "center", // 水平居中
                        verticalAlignment: "middle", // 垂直居中
                        // numberFormat: "0.00", // 数字格式
                        // protection: true // 启用保护
                    });

                    initNum = columnEnum[parseInt(temp) + parseInt(temp2)]

                    for (let cut = 1; cut <= numInfo[initArray[j]][h]; cut++) {

                        sheet.cell(init + "3").value(t)
                        sheet.cell(init + "3").style({
                            fontColor: "#0d0d0d", // 字体颜色为黑色
                            fontSize: 15, // 字体大小为14
                            bold: true, // 加粗
                            italic: true, // 倾斜
                            underline: true, // 下划线
                            border: {
                                bottom: { style: "thin", color: "000000" }, // 底部边框
                                top: { style: "thin", color: "000000" }, // 顶部边框
                                left: { style: "thin", color: "000000" }, // 左侧边框
                                right: { style: "thin", color: "000000" } // 右侧边框
                            },
                            horizontalAlignment: "center", // 水平居中
                            verticalAlignment: "middle", // 垂直居中
                            // numberFormat: "0.00", // 数字格式
                            // protection: true // 启用保护
                        });

                        // 将内容居中

                        t++
                        const temp3 = getKeyByValue(columnEnum, init)
                        init = columnEnum[parseInt(temp3) + 1]
                    }
                }

            }
            workbook.toFileAsync("./output.xlsx")
            return workbook.outputAsync();
        })
        .then(data => {
            res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            res.setHeader('Content-Disposition', 'attachment; filename=output.xlsx');
            res.json({
                "code": 200,
                "content": data,
                "targetlength": targetNum.length
            })
        })
        .catch(error => {
            console.error('Failed to generate Excel file:', error);
            res.status(500).json({ error: 'Failed to generate Excel file.' });
        });



}


// const { T_exam_target } = require('../models/t_exam_target')

exports.outputAchievePrasePage = async (req, res) => {
    try {
        const subId = req.query.subId
        const paperType = req.query.paperType

        const data = req.body.DataToSubmit

        const studentCount = req.body.studentCount
        console.log(data);
        console.log(studentCount);
        const targetMes = await T_exam_target.findAll({
            where: {
                sub_id: subId,
            },
            attributes: ['id', 'target_desc', 'target_content', 'target_name']
        })
        const targetMap = targetMes.reduce((acc, cur) => {
            acc[cur.id] = cur.target_name;
            return acc;
        }, {});


        const targetDetileMes = await T_exam_target_to_question.findAll({
            where: {
                subid: subId,
                paperType: paperType
            },
            attributes: ['targetId', 'score', 'questionType']
        })
        const totalScoresByTargetId = targetDetileMes.reduce((acc, cur) => {
            const { targetId, score } = cur;
            if (acc[targetId]) {
                acc[targetId] += score;
            } else {
                acc[targetId] = score;
            }
            return acc;
        }, {});
        // console.log("ahhah");

        // console.log("total", totalScoresByTargetId);

        const newData = {};
        for (const key in totalScoresByTargetId) {
            if (key in targetMap) {
                const targetName = targetMap[key];
                newData[targetName] = totalScoresByTargetId[key];
            }
        }

        console.log(newData);

        const endData = data.map(obj => {
            const { type, score } = obj;
            if (type in newData) {
                const divisor = newData[type];
                obj.score = score / divisor;
            }
            return obj;
        });

        // console.log(endData);

        function sortByTypeNumber(a, b) {
            // 提取 type 后面的数字
            const typeNumberA = parseFloat(a.type.split('课程目标')[1]);
            const typeNumberB = parseFloat(b.type.split('课程目标')[1]);

            // 比较数字并返回排序结果
            return typeNumberA - typeNumberB;
        }
        const sortedObjectives = endData.sort(sortByTypeNumber);
        const scores = sortedObjectives.map(item => item.score);
        //console.log(sortedObjectives);
        console.log("score", scores);
        const targetObjects = targetMes.map(target => ({
            targetDesc: target.target_desc,
            targetContent: target.target_content,
            targetName: target.target_name
        }));


        // console.log(targetObjects);
        // 初始化列标题数组s
        const valuesArray = Object.values(newData);

        const workbook = XlsxPopulate.fromBlankAsync()
            .then(workbook => {
                const sheet = workbook.sheet(0)
                sheet.cell("A6").value("总分")
                sheet.cell("A7").value("平均分")
                sheet.cell("A8").value("达成度")
                sheet.range("B1:D1").merged(true)

                sheet.cell('B1').value("试卷目标基本分析情况").style({
                    wrapText: true,
                    fontColor: "#0d0d0d", // 字体颜色为黑色
                    fontSize: 14, // 字体大小为14
                    verticalAlignment: "middle", // 垂直居中

                });

                let initChar = 'B'
                let initNum = 2
                for (let i = 0; i < targetObjects.length; i++) {
                    const beforeChar = columnEnum[parseInt(getKeyByValue(columnEnum, initChar)) + parseInt(5)]
                    // console.log("before = ", beforeChar);
                    const cell_range = initChar + initNum + ":" + beforeChar + (initNum + 3)
                    const cell_range2 = initChar + (initNum + 4) + ":" + beforeChar + (initNum + 4)
                    const cell_range3 = initChar + (initNum + 5) + ":" + beforeChar + (initNum + 5)
                    const cell_range4 = initChar + (initNum + 6) + ":" + beforeChar + (initNum + 6)


                    // console.log("cell_range = ", cell_range);
                    const cell = sheet.cell(initChar + initNum)
                    const cell2 = sheet.cell(initChar + (initNum + 4))
                    const cell3 = sheet.cell(initChar + (initNum + 5))
                    const cell4 = sheet.cell(initChar + (initNum + 6))

                    cell2.value(valuesArray[i])
                    cell.value(targetObjects[i].targetName + ":" + targetObjects[i].targetContent + "(" + targetObjects[i].targetDesc + ")")
                    cell4.value(scores[i])
                    cell3.value(scores[i] * valuesArray[i])
                    sheet.range(cell_range).merged(true);
                    sheet.range(cell_range2).merged(true)
                    sheet.range(cell_range3).merged(true)
                    sheet.range(cell_range4).merged(true)
                    cell.style({
                        wrapText: true,
                        fontColor: "#0d0d0d", // 字体颜色为黑色
                        fontSize: 14, // 字体大小为14
                        // bold: true, // 加粗
                        // italic: true, // 倾斜
                        // underline: true, // 下划线
                        // horizontalAlignment: "center", // 水平居中
                        verticalAlignment: "middle", // 垂直居中
                        // numberFormat: "0.00", // 数字格式
                        // protection: true // 启用保护
                    });
                    // initChar = columnEnum[getKeyByValue(initChar) + 6]
                    sheet.range(cell_range).style({
                        border: {
                            bottom: { style: "thin", color: "000000" }, // 底部边框
                            top: { style: "thin", color: "000000" }, // 顶部边框
                            left: { style: "thin", color: "000000" }, // 左侧边框
                            right: { style: "thin", color: "000000" } // 右侧边框
                        }
                    });
                    initChar = columnEnum[parseInt(getKeyByValue(columnEnum, initChar)) + parseInt(6)]
                }



                try {
                    workbook.toFileAsync("./praseOutput.xlsx")
                } catch (error) {
                    res.json({
                        "error": "locked"
                    })
                }

                return workbook.outputAsync();
            })
            .then(data => {
                res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                res.setHeader('Content-Disposition', 'attachment; filename=output.xlsx');
                res.json({
                    "code": 200,
                    "content": data
                })
            })
            .catch(error => {
                console.error('Failed to generate Excel file:', error);
                res.status(500).json({ error: 'Failed to generate Excel file.' });
            });

    } catch (error) {

    }




}